package com.qf.dao.impl;

import com.qf.dao.EmpDao;
import com.qf.entity.Emp;
import com.qf.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EmpDaoImpl implements EmpDao {
    private QueryRunner qr= DBUtils.DB.getQr();


    @Override
    public Integer selectEmpCount(String e_name, String e_phone_number) throws SQLException {
        String sql="select * from emp where 1=1";
        StringBuffer sb=new StringBuffer(sql);
        List list=new ArrayList();
        if (e_name!=null && !"".equals(e_name)){
            sb.append(" and e_name like ?");
            list.add("%"+e_name+"%");
        }
        if (e_phone_number!=null && !"".equals(e_phone_number)){
            sb.append(" and e_phone_number =?");
            list.add(e_phone_number);
        }

        Integer count;
       if (list!=null && list.size()>0){
           count = (Integer) qr.query(sb.toString(),new ScalarHandler(),list.toArray());
       }else {
           count = (Integer) qr.query(sb.toString(),new ScalarHandler());
       }
        return count;
    }

    @Override
    public List<Emp> selectEmp(String e_name, String e_phone_number, Integer pageSize, Integer currentPageNo) throws SQLException {
        String sql="select * from emp where 1=1";
        StringBuffer sb=new StringBuffer(sql);
        List list=new ArrayList();
        if (e_name!=null && !"".equals(e_name)){
            sb.append(" and e_name like ?");
            list.add("%"+e_name+"%");
        }
        if (e_phone_number!=null && !"".equals(e_phone_number)){
            sb.append(" and e_phone_number =?");
            list.add(e_phone_number);
        }


        sb.append(" limit ?,?");
        list.add((currentPageNo-1)*pageSize);
        list.add(pageSize);

        List<Emp> empList = qr.query(sb.toString(), new BeanListHandler<Emp>(Emp.class), list.toArray());

        return empList;
    }

    @Override
    public Emp selectEmpById(int e_id) throws SQLException {
        String sql="select * from emp where e_id=?";
        Emp emp = qr.query(sql, new BeanHandler<>(Emp.class), e_id);
        return emp;
    }

    @Override
    public int updateEmp(Emp emp) throws SQLException {
        String sql="update emp set e_photo=?,e_hiredate=?,e_password=? where e_id=?";
        Object [] objects={emp.getE_photo(),emp.getE_hiredate(),emp.getE_password(),emp.getE_id()};
        int num = qr.update(sql, objects);
        return num;
    }

    @Override
    public int addEmp(Emp emp) throws SQLException {
        String sql="insert into emp (e_name,e_phone_number,e_photo,e_password,e_hiredate) values(?,?,?,?,?)";
        Object [] objects={emp.getE_name(),emp.getE_phone_number(),emp.getE_photo(),emp.getE_password(),emp.getE_hiredate()};
        int num = qr.update(sql, objects);
        return num;
    }

    @Override
    public int deleteEmp(int e_id) throws SQLException {
        String sql="delete from emp where e_id=?";
        int num = qr.update(sql, e_id);
        return num;
    }
}
